package com.cheneyjoo.usecase;

import com.alibaba.fastjson.parser.deserializer.SqlDateDeserializer;
import com.cheneyjoo.model.STUDENT;
import com.cheneyjoo.model.STUDENT_CLASS;
import com.cheneyjoo.service.IClassStudentsService;
import com.cheneyjoo.service.IStudentClassService;
import com.cheneyjoo.tools.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.*;

public class StudentClassUseCase implements IStudentClassService, IClassStudentsService {
    private final QueryRunner qr;

    public StudentClassUseCase() {
        qr = new QueryRunner(DruidUtils.DATASOURCE);
    }

    /**
     * 获取没有班级的学生
     *
     * @return 没有班级的学生列表
     */
    @Override
    public List<STUDENT> getNoClassStudents() {
        try {
            String sql = "SELECT * FROM STUDENT WHERE CLASS_ID=0";
            return qr.query(sql, new BeanListHandler<>(STUDENT.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 分配学生入班级
     *
     * @param studentIds 学生ID列表
     * @param classId    班级ID
     * @return 是否成功
     */
    @Override
    public boolean distributionStudentClass(String studentIds, int classId) {
        try {
            String SQL = "update STUDENT set CLASS_ID=? where ID in(" + studentIds + ")";
            if (qr.update(SQL, classId) > 0){
                SQL = "update STUDENT_CLASS set STUDENT_COUNT=(SELECT COUNT(1) FROM STUDENT WHERE CLASS_ID=STUDENT_CLASS.`ID`) where ID=?";
                return qr.update(SQL, classId) > 0;
            }else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 移除班级学生
     *
     * @param studentIds 学生列表
     * @return 是否成功
     */
    @Override
    public boolean removeClassStudent(String studentIds, int classId) {
        try {
            String SQL = "update STUDENT set CLASS_ID=0 where ID in(" + studentIds + ")";
            if (qr.update(SQL) > 0){
                SQL = "update STUDENT_CLASS set STUDENT_COUNT=(SELECT COUNT(1) FROM STUDENT WHERE CLASS_ID=STUDENT_CLASS.`ID`) where ID=?";
                return qr.update(SQL, classId) > 0;
            }else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 获取班级学生列表
     * @return 学生列表
     */
    @Override
    public Map<String, Object>  getClassStudents(int pageSize, int currPage, Map<String, String> where) {
        StringBuilder sql = new StringBuilder();
        StringBuilder sqlCount = new StringBuilder();
        List<String> param = new ArrayList<>();
        sql.append("SELECT * ");
        sqlCount.append("SELECT count(1) ");

        StringBuilder otherSql = new StringBuilder();
        otherSql.append(" FROM STUDENT WHERE CLASS_ID=? ");
        param.add( where.get("classid"));

        //拼接查询条件
        if (where.containsKey("no") && Optional.ofNullable(where.get("no")).isPresent()) {
            otherSql.append(" and STUDENT_NO like ? ");
            param.add("%" + where.get("no") + "%");
        }

        if (where.containsKey("name") && Optional.ofNullable(where.get("name")).isPresent()) {
            otherSql.append(" and STUDENT_NAME like ? ");
            param.add("%" + where.get("name") + "%");
        }

        if (where.containsKey("sex") && Optional.ofNullable(where.get("sex")).isPresent()) {
            otherSql.append(" and STUDENT_SEX = ? ");
            param.add(where.get("sex"));
        }
        //拼接条件
        sqlCount.append(otherSql.toString());
        sql.append(otherSql.toString());

        //拼接排序
        sql.append(" order by id desc");
        //拼接分页
        sql.append("  limit ").append((currPage - 1) * pageSize).append(",").append(pageSize);

        Long queryCount = 0L;
        List<STUDENT> studentList = null;
        try {
            queryCount = qr.query(sqlCount.toString(), new ScalarHandler<Long>(), param.toArray());
            studentList = qr.query(sql.toString(), new BeanListHandler<STUDENT>(STUDENT.class), param.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //计算共有多少页
        int totalPage = 0;
        totalPage = Double.valueOf(Math.ceil(queryCount * 1.0 / pageSize * 1.0)).intValue();
        totalPage = totalPage == 0 ? 1 : totalPage;

        if (totalPage < currPage) {
            currPage = Math.toIntExact(totalPage);
        }
        Map<String, Object> result = new HashMap<>();
        result.put("table", studentList);
        result.put("totalRow", queryCount);
        result.put("totalPage", totalPage);
        return result;
    }

    /**
     * 添加班级信息
     *
     * @param student_class 班级对象
     * @return 是否成功
     */
    @Override
    public boolean addStudentClass(STUDENT_CLASS student_class) {
        try {
            String existsSql = "select count(1) from STUDENT_CLASS where CLASS_NO=?";
            Long count = qr.query(existsSql, new ScalarHandler<Long>(), student_class.getClass_no());
            if (count > 0) {
                return false;
            }
            String sql = "INSERT INTO `STUDENT_CLASS` (`ID`,`CLASS_NO`,`CLASS_NAME`,`GRADE`,`TEACHER_NAME`,`CLASS_SLOGAN`,`STUDENT_COUNT`)" +
                    " VALUES(null,?,?,?,?,?,0)";
            Object[] objects = new Object[]{
                    student_class.getClass_no(),
                    student_class.getClass_name(),
                    student_class.getGrade(),
                    student_class.getTeacher_name(),
                    student_class.getClass_slogan()
            };
            return qr.update(sql, objects) > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 修改班级信息
     *
     * @param student_class 班级对象
     * @return 是否成功
     */
    @Override
    public boolean updateStudentClass(STUDENT_CLASS student_class) {
        try {
            String existsSql = "select count(1) from STUDENT_CLASS where ID=?";
            Long count = qr.query(existsSql, new ScalarHandler<Long>(), student_class.getId());
            if (count > 0) {
                String SQL = "UPDATE `STUDENT_CLASS`" +
                        "SET " +
                        "  `CLASS_NO` = ?," +
                        "  `CLASS_NAME` = ?," +
                        "  `GRADE` = ?," +
                        "  `TEACHER_NAME` = ?," +
                        "  `CLASS_SLOGAN` = ?" +
                        "WHERE `ID` = ?";
                Object[] objects = new Object[]{
                        student_class.getClass_no(),
                        student_class.getClass_name(),
                        student_class.getGrade(),
                        student_class.getTeacher_name(),
                        student_class.getClass_slogan(),
                        student_class.getId(),
                };
                return qr.update(SQL, objects) > 0;
            } else {
                return false;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 删除班级信息
     *
     * @param id 主键ID
     * @return 是否成功
     */
    @Override
    public boolean deleteStudentClass(int id) {
        try {
            String existsSql = "select count(1) from STUDENT where CLASS_ID=?";
            Long count = qr.query(existsSql, new ScalarHandler<Long>(), id);
            if (count > 0) {
                return false;
            }
            String sql = "delete from `STUDENT_CLASS` where ID =?";
            return qr.update(sql,id)>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 查询班级信息
     * @param id 主键ID
     * @return 班级信息
     */
    @Override
    public STUDENT_CLASS getStudentClass(int id) {
        try {
            String sql="select * from `STUDENT_CLASS` where ID=?";
           return qr.query(sql,new BeanHandler<>(STUDENT_CLASS.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 获取班级列表
     * @param pageSize
     * @param currPage
     * @param where where条件键值队
     * @return
     */
    @Override
    public Map<String, Object> getStudentClasses(int pageSize, int currPage, Map<String, String> where) {
        StringBuilder sql = new StringBuilder();
        StringBuilder sqlCount = new StringBuilder();
        sql.append("SELECT * ");
        sqlCount.append("SELECT count(1) ");

        StringBuilder otherSql = new StringBuilder();
        otherSql.append(" FROM `STUDENT_CLASS` WHERE 1=1 ");

        List<String> param = new ArrayList<>();
        //拼接查询条件
        if (where.containsKey("no") && Optional.ofNullable(where.get("no")).isPresent()) {
            otherSql.append(" and CLASS_NO like ? ");
            param.add("%" + where.get("no") + "%");
        }

        if (where.containsKey("name") && Optional.ofNullable(where.get("name")).isPresent()) {
            otherSql.append(" and CLASS_NAME like ? ");
            param.add("%" + where.get("name") + "%");
        }

        if (where.containsKey("teachername") && Optional.ofNullable(where.get("teachername")).isPresent()) {
            otherSql.append(" and TEACHER_NAME like ? ");
            param.add("%" + where.get("teachername") + "%");
        }
        //拼接条件
        sqlCount.append(otherSql.toString());
        sql.append(otherSql.toString());

        //拼接排序
        sql.append(" order by id desc");
        //拼接分页
        sql.append("  limit ").append((currPage - 1) * pageSize).append(",").append(pageSize);

        Long queryCount = 0L;
        List<STUDENT_CLASS> studentClassList = null;
        try {
            queryCount = qr.query(sqlCount.toString(), new ScalarHandler<Long>(), param.toArray());
            studentClassList = qr.query(sql.toString(), new BeanListHandler<STUDENT_CLASS>(STUDENT_CLASS.class), param.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //计算共有多少页
        int totalPage = 0;
        totalPage = Double.valueOf(Math.ceil(queryCount * 1.0 / pageSize * 1.0)).intValue();
        totalPage = totalPage == 0 ? 1 : totalPage;

        if (totalPage < currPage) {
            currPage = Math.toIntExact(totalPage);
        }
        Map<String, Object> result = new HashMap<>();
        result.put("table", studentClassList);
        result.put("totalRow", queryCount);
        result.put("totalPage", totalPage);
        return result;
    }
}
